IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_PageControlChangeTemplateRegions]')
              AND TYPE IN (N'P', N'PC')
   )
    DROP PROCEDURE [dbo].[cms_sp_PageControlChangeTemplateRegions]
GO
CREATE PROCEDURE [dbo].[cms_sp_PageControlChangeTemplateRegions]
(@PreviousPageId INT, @LatestPageId INT)
AS
BEGIN
	DECLARE @oldTemplateId INT
	DECLARE @newTemplateId INT
	
	SELECT @oldTemplateId = p.TemplateId
	FROM   Pages AS p
	WHERE  p.PageId = @PreviousPageId
	
	SELECT @newTemplateId = p.TemplateId
	FROM   Pages AS p
	WHERE  p.PageId = @LatestPageId;
	
	WITH temp(TemplateRegionId, TemplateRegionName)
	     AS
	     (
	         SELECT tr.TemplateRegionId,
	                tr.TemplateRegionName
	         FROM   TemplateRegions AS tr
	         WHERE  tr.TemplateId = @oldTemplateId
	     )
	
	UPDATE PageControls
	SET    TemplateRegionId = tr.TemplateRegionId
	FROM   PageControls AS pc
	       JOIN temp AS t
	            ON  t.TemplateRegionId = pc.TemplateRegionId
	            AND pc.PageId = @LatestPageId
	       JOIN TemplateRegions AS tr
	            ON  t.TemplateRegionName = tr.TemplateRegionName
	            AND tr.TemplateId = @newTemplateId;
	-- Remove unnecessary controls
	WITH temp2(TemplateRegionId) 
	     AS 
	     (
	         SELECT tr.TemplateRegionId
	         FROM   TemplateRegions AS tr
	         WHERE  tr.TemplateId = @newTemplateId
	     ) 
	
	DELETE PageControls
	FROM   PageControls AS pc
	WHERE  pc.PageId = @LatestPageId
	       AND pc.TemplateRegionId NOT IN (SELECT t.TemplateRegionId
	                                       FROM   temp2 AS t)
END